import pymysql
import random
from datetime import datetime
import os
import configparser


# 生成区域指数

# 获取当前脚本所在目录的绝对路径
script_dir = os.path.dirname(os.path.abspath(__file__))

config = configparser.ConfigParser()
config.read(os.path.join(script_dir, 'config.ini'))

url_section = config['MYSQL']
mysql_host = url_section['mysql_host']
mysql_port = url_section['mysql_port']
mysql_user = url_section['mysql_user']
mysql_password = url_section['mysql_password']
mysql_database = url_section['mysql_database']
default_area_code = url_section['default_area_code']

# 连接到数据库
conn = pymysql.connect(host=mysql_host,
                       port=int(mysql_port),
                       user=mysql_user,
                       password=mysql_password,
                       database=mysql_database
                       )
cursor = conn.cursor()

# 初始化一个空列表来存储所有区域

def find_all_descendants_titles(cursor, start_id):
    """
    使用广度优先搜索（BFS）查找给定起始id的所有子孙区域的title。

    :param cursor: MySQL游标对象
    :param start_id: 起始区域的id
    :return: 一个包含所有子孙区域title的列表
    """
    descendants_titles = []  # 用于存储已找到的子孙区域title
    queue = [(start_id,)]  # 初始化队列，将起始id（作为元组）放入队列，以便后续扩展

    while queue:
        current_id, = queue.pop(0)  # 从队列中取出一个id（作为元组，所以需要解包）

        # 查询当前id的所有直接子区域及其title
        try:
            cursor.execute("SELECT id, title FROM region WHERE parent_id = %s", (current_id,))
            subregions = cursor.fetchall()  # 获取查询结果

            # 将每个子区域的title添加到结果列表中
            for child_id, title in subregions:
                descendants_titles.append(title)
                # 将子区域的id添加到队列中以便后续处理其子孙区域,因只查询市级不查询区及故注释掉
                # queue.append((child_id,))
        except Error as e:
            print(f"Error querying descendants for id {current_id}: {e}")
            # 可以选择在这里记录错误、退出函数或继续处理其他id

    return descendants_titles


# 检查 index_area 表中是否有对应数据，没有则插入
def check_or_insert_index_area(region_list, current_date):
    for region in region_list:
        query = """
        SELECT `index` FROM index_area
        WHERE city = %s AND date = %s
        """
        cursor.execute(query, (region, current_date))
        result = cursor.fetchone()

        if result is None:
            # 生成一个 0-100 的数
            new_finance = random.randint(40, 60)
            new_public_opinion = random.randint(30, 50)
            new_transportation_population = random.randint(20, 35)
            new_material_reserve = random.randint(10, 30)
            # 插入新数据到 index_area
            insert_query = """
            INSERT INTO index_area (city, date,finance, public_opinion, transportation_population, material_reserve)
            VALUES (%s, %s, %s, %s, %s, %s)
            """
            cursor.execute(insert_query, (region, current_date, new_finance, new_public_opinion, new_transportation_population, new_material_reserve))
            conn.commit()
            print(f"Inserted new finance {new_finance} new public_opinion {new_public_opinion} new transportation_population {new_transportation_population} new material_reserve {new_material_reserve} for {region} on {current_date}")
        else:
            print(f"Existing index {result[0]} for {region} on {current_date}")


# 主程序
if __name__ == "__main__":
    adcode_to_query = '24'
    current_date = datetime.now().strftime('%Y-%m-%d')

    all_regions = find_all_descendants_titles(cursor, adcode_to_query)
    check_or_insert_index_area(all_regions, current_date)

    # 关闭数据库连接
    cursor.close()
    conn.close()
